﻿CREATE Trigger [dbo].[tr_Borrower_Format]
On [dbo].[t_Borrower]
For Insert,UPDATE
As


DECLARE @RC int SET @RC = @@ROWCOUNT
IF @RC = 0 RETURN
IF TRIGGER_NESTLEVEL(@@PROCID) > 1 RETURN

DECLARE @IsMultiLoan bit SET @IsMultiLoan = 0
SELECT @IsMultiLoan = COUNT(*)
FROM t_Loan_Borrower AS L_B
INNER JOIN inserted AS B ON L_B.BorrowerID = B.ID
WHERE ISNULL(SSN,'') > ''
GROUP BY L_B.BorrowerID
HAVING      (COUNT(*) > 1)

DECLARE @ErrorMsg sysname

IF UPDATE (SSN)BEGIN
	IF @RC > 1 BEGIN
		ROLLBACK
		RAISERROR('Multi-row updates of SSN are not supported',16,1)
		RETURN
	END
	IF @IsMultiLoan = 1 AND EXISTS(SELECT * FROM inserted WHERE dbo.IsEdit(SSN) = 0)
		SELECT @ErrorMsg = SSN FROM inserted
	ELSE BEGIN
		INSERT INTO v_Borrower_ProcessSSN	SELECT i.ID,d.SSN FROM inserted i FULL OUTER JOIN deleted d ON i.ID = d.ID
		IF @@ERROR <> 0 RETURN

		INSERT INTO v_Borrower_CheckVIN	SELECT ID,0 FROM inserted
		IF @@ERROR <> 0 RETURN

		UPDATE CI SET Pulled = 0
		FROM t_CreditInfo CI INNER JOIN inserted i ON i.ID = CI.BorrowerID
		WHERE Pulled <> 0
	END
END

IF UPDATE(FirstName)
	IF @IsMultiLoan = 1 AND EXISTS(SELECT * FROM inserted WHERE dbo.IsEdit(FirstName) = 0)
		SELECT @ErrorMsg = FirstName FROM inserted
	ELSE
		UPDATE a SET FirstName = master.dbo.INITCAP(dbo.CleanEditValue(a.FirstName))
		FROM t_Borrower a INNER JOIN inserted i ON a.ID = i.ID
		WHERE a.FirstName <> master.dbo.INITCAP(dbo.CleanEditValue(a.FirstName))

IF UPDATE(LastName)
	IF @IsMultiLoan = 1 AND EXISTS(SELECT * FROM inserted WHERE dbo.IsEdit(LastName) = 0)
		SELECT @ErrorMsg = LastName FROM inserted
	ELSE
		UPDATE a SET LastName = master.dbo.INITCAP(dbo.CleanEditValue(a.LastName))
		FROM t_Borrower a INNER JOIN inserted i ON a.ID = i.ID
		WHERE a.LastName <> master.dbo.INITCAP(dbo.CleanEditValue(a.LastName))

IF @ErrorMsg IS NULL RETURN

ROLLBACK
RAISERROR('This borrower belongs to multiple loans.
If you want to enter a new borrower, remove this one from this loan.
If you want to correct wrong information use [%s] notation.',16,1,@ErrorMsg)

